﻿--0
SELECT REGION,
	'REGION' ROLLUP_TYPE,
	MAX(MARKET) MARKET,
	MAX(STATE_CD) STATE_CD, 
	MAX(ACCT_MGR) ACCT_MGR, 
	MAX(AREA_MGR) AREA_MGR,	
	MAX(PORTFOLIO_NM) PORTFOLIO_NM,
	REGION PROPERTY_NM, 
	COUNT(TRAC_ID) TRAC_ID, 
	SUM(ADDRESSES) ADDRESSES, 
	SUM(GREENS) GREENS, 
	SUM(SUBSCRIBERS) SUBSCRIBERS, 
	SUM(HSIA) HSIA, 
	SUM(IPTV) IPTV, 
	SUM(VOIP) VOIP, 
	SUM(HSIA_REVENUE) HSIA_REVENUE, 
	SUM(IPTV_REVENUE) IPTV_REVENUE, 
	SUM(VOIP_REVENUE) VOIP_REVENUE, 
	SUM(OTHER_UVERSE_REVENUE) OTHER_UVERSE_REVENUE, 
	SUM(TOTAL_REVENUE) TOTAL_REVENUE,
	100*SUM(GREENS) /((SUM(ADDRESSES) + 0.01)) LIT,
	100*SUM(SUBSCRIBERS)/((SUM(ADDRESSES) + 0.01)) INSERVICE,
    100*(INSERVICE/(LIT+0.01)) SATURATION,    
	100*SUM(IPTV_REVENUE) /(SUM(TOTAL_REVENUE) + 0.01) IPTVPercentage,
	100*SUM(HSIA_REVENUE) /((SUM(TOTAL_REVENUE) + 0.01)) HSIAPercentage,
	100*SUM(VOIP_REVENUE) /((SUM(TOTAL_REVENUE) + 0.01))  VOIPPercentage,
	100*SUM(OTHER_UVERSE_REVENUE) /((SUM(TOTAL_REVENUE) + 0.01))  OtherPercentage,
	SUM(TOTAL_REVENUE) /(SUM(SUBSCRIBERS) + 0.01)  TotalPerSubscriber,
	SUM(IPTV_REVENUE) /(SUM(IPTV) + 0.01) IPTVPerSubscriber,
	SUM(HSIA_REVENUE) /(SUM(HSIA) + 0.01) HSIAPerSubscriber,
	SUM(VOIP_REVENUE) /(SUM(VOIP) + 0.01)  VOIPPerSubscriber,
	SUM(OTHER_UVERSE_REVENUE) /(SUM(SUBSCRIBERS) + 0.01)  OtherPerSubscriber
    FROM ({0}) AS DETAIL_ROWS
GROUP	BY REGION, ROLLUP_TYPE
ORDER BY REGION
;
--1
SELECT 
	MARKET,
	'MARKET' ROLLUP_TYPE,
	MAX(REGION) REGION,
	MAX(STATE_CD) STATE_CD, 
	MAX(ACCT_MGR) ACCT_MGR, 
	MAX(AREA_MGR) AREA_MGR,	
	MAX(PORTFOLIO_NM) PORTFOLIO_NM,
	MARKET PROPERTY_NM, 
	COUNT(TRAC_ID) TRAC_ID, 
	SUM(ADDRESSES) ADDRESSES, 
	SUM(GREENS) GREENS, 
	SUM(SUBSCRIBERS) SUBSCRIBERS, 
	SUM(HSIA) HSIA, 
	SUM(IPTV) IPTV, 
	SUM(VOIP) VOIP, 
	SUM(HSIA_REVENUE) HSIA_REVENUE, 
	SUM(IPTV_REVENUE) IPTV_REVENUE, 
	SUM(VOIP_REVENUE) VOIP_REVENUE, 
	SUM(OTHER_UVERSE_REVENUE) OTHER_UVERSE_REVENUE, 
	SUM(TOTAL_REVENUE) TOTAL_REVENUE,
	100*SUM(GREENS) /((SUM(ADDRESSES) + 0.01)) LIT,
	100*SUM(SUBSCRIBERS)/((SUM(ADDRESSES) + 0.01)) INSERVICE,
    100*(INSERVICE/(LIT+0.01)) SATURATION,    
	100*SUM(IPTV_REVENUE) /(SUM(TOTAL_REVENUE) + 0.01) IPTVPercentage,
	100*SUM(HSIA_REVENUE) /((SUM(TOTAL_REVENUE) + 0.01)) HSIAPercentage,
	100*SUM(VOIP_REVENUE) /((SUM(TOTAL_REVENUE) + 0.01))  VOIPPercentage,
	100*SUM(OTHER_UVERSE_REVENUE) /((SUM(TOTAL_REVENUE) + 0.01))  OtherPercentage,
	SUM(TOTAL_REVENUE) /(SUM(SUBSCRIBERS) + 0.01)  TotalPerSubscriber,
	SUM(IPTV_REVENUE) /(SUM(IPTV) + 0.01) IPTVPerSubscriber,
	SUM(HSIA_REVENUE) /(SUM(HSIA) + 0.01) HSIAPerSubscriber,
	SUM(VOIP_REVENUE) /(SUM(VOIP) + 0.01)  VOIPPerSubscriber,
	SUM(OTHER_UVERSE_REVENUE) /(SUM(SUBSCRIBERS) + 0.01)  OtherPerSubscriber
FROM ({0}) AS DETAIL_ROWS
GROUP	BY MARKET, ROLLUP_TYPE
ORDER BY MARKET
;
--2
SELECT 
	STATE_CD,
	'STATE_CD' ROLLUP_TYPE,
	MAX(REGION)  REGION,
	MAX(MARKET) MARKET,	
	MAX(ACCT_MGR) ACCT_MGR, 
	MAX(AREA_MGR) AREA_MGR,	
	MAX(PORTFOLIO_NM) PORTFOLIO_NM,
	STATE_CD PROPERTY_NM, 
	COUNT(TRAC_ID) TRAC_ID, 
	SUM(ADDRESSES) ADDRESSES, 
	SUM(GREENS) GREENS, 
	SUM(SUBSCRIBERS) SUBSCRIBERS, 
	SUM(HSIA) HSIA, 
	SUM(IPTV) IPTV, 
	SUM(VOIP) VOIP, 
	SUM(HSIA_REVENUE) HSIA_REVENUE, 
	SUM(IPTV_REVENUE) IPTV_REVENUE, 
	SUM(VOIP_REVENUE) VOIP_REVENUE, 
	SUM(OTHER_UVERSE_REVENUE) OTHER_UVERSE_REVENUE, 
	SUM(TOTAL_REVENUE) TOTAL_REVENUE,
	100*SUM(GREENS) /((SUM(ADDRESSES) + 0.01)) LIT,
	100*SUM(SUBSCRIBERS)/((SUM(ADDRESSES) + 0.01)) INSERVICE,
    100*(INSERVICE/(LIT+0.01)) SATURATION,    
	100*SUM(IPTV_REVENUE) /(SUM(TOTAL_REVENUE) + 0.01) IPTVPercentage,
	100*SUM(HSIA_REVENUE) /((SUM(TOTAL_REVENUE) + 0.01)) HSIAPercentage,
	100*SUM(VOIP_REVENUE) /((SUM(TOTAL_REVENUE) + 0.01))  VOIPPercentage,
	100*SUM(OTHER_UVERSE_REVENUE) /((SUM(TOTAL_REVENUE) + 0.01))  OtherPercentage,
	SUM(TOTAL_REVENUE) /(SUM(SUBSCRIBERS) + 0.01)  TotalPerSubscriber,
	SUM(IPTV_REVENUE) /(SUM(IPTV) + 0.01) IPTVPerSubscriber,
	SUM(HSIA_REVENUE) /(SUM(HSIA) + 0.01) HSIAPerSubscriber,
	SUM(VOIP_REVENUE) /(SUM(VOIP) + 0.01)  VOIPPerSubscriber,
	SUM(OTHER_UVERSE_REVENUE) /(SUM(SUBSCRIBERS) + 0.01)  OtherPerSubscriber
FROM ({0}) AS DETAIL_ROWS
GROUP	BY STATE_CD, ROLLUP_TYPE
ORDER BY STATE_CD
;
--3
SELECT 
	AREA_MGR,
	'AREA_MGR' ROLLUP_TYPE,
	MAX(REGION)  REGION,
	MAX(MARKET) MARKET,	
	MAX(STATE_CD) STATE_CD, 
	MAX(ACCT_MGR) ACCT_MGR, 
	MAX(PORTFOLIO_NM) PORTFOLIO_NM,
	AREA_MGR PROPERTY_NM, 
	COUNT(TRAC_ID) TRAC_ID, 
	SUM(ADDRESSES) ADDRESSES, 
	SUM(GREENS) GREENS, 
	SUM(SUBSCRIBERS) SUBSCRIBERS, 
	SUM(HSIA) HSIA, 
	SUM(IPTV) IPTV, 
	SUM(VOIP) VOIP, 
	SUM(HSIA_REVENUE) HSIA_REVENUE, 
	SUM(IPTV_REVENUE) IPTV_REVENUE, 
	SUM(VOIP_REVENUE) VOIP_REVENUE, 
	SUM(OTHER_UVERSE_REVENUE) OTHER_UVERSE_REVENUE, 
	SUM(TOTAL_REVENUE) TOTAL_REVENUE,
	100*SUM(GREENS) /((SUM(ADDRESSES) + 0.01)) LIT,
	100*SUM(SUBSCRIBERS)/((SUM(ADDRESSES) + 0.01)) INSERVICE,
    100*(INSERVICE/(LIT+0.01)) SATURATION,    
	100*SUM(IPTV_REVENUE) /(SUM(TOTAL_REVENUE) + 0.01) IPTVPercentage,
	100*SUM(HSIA_REVENUE) /((SUM(TOTAL_REVENUE) + 0.01)) HSIAPercentage,
	100*SUM(VOIP_REVENUE) /((SUM(TOTAL_REVENUE) + 0.01))  VOIPPercentage,
	100*SUM(OTHER_UVERSE_REVENUE) /((SUM(TOTAL_REVENUE) + 0.01))  OtherPercentage,
	SUM(TOTAL_REVENUE) /(SUM(SUBSCRIBERS) + 0.01)  TotalPerSubscriber,
	SUM(IPTV_REVENUE) /(SUM(IPTV) + 0.01) IPTVPerSubscriber,
	SUM(HSIA_REVENUE) /(SUM(HSIA) + 0.01) HSIAPerSubscriber,
	SUM(VOIP_REVENUE) /(SUM(VOIP) + 0.01)  VOIPPerSubscriber,
	SUM(OTHER_UVERSE_REVENUE) /(SUM(SUBSCRIBERS) + 0.01)  OtherPerSubscriber
FROM ({0}) AS DETAIL_ROWS
GROUP	BY AREA_MGR, ROLLUP_TYPE
ORDER BY AREA_MGR
;
--4
SELECT 
	ACCT_MGR,
	'ACCT_MGR' ROLLUP_TYPE,
	MAX(REGION)  REGION,
	MAX(MARKET) MARKET,	
	MAX(STATE_CD) STATE_CD, 
	MAX(AREA_MGR) AREA_MGR,	
	MAX(PORTFOLIO_NM) PORTFOLIO_NM,
	ACCT_MGR PROPERTY_NM, 
	COUNT(TRAC_ID) TRAC_ID, 
	SUM(ADDRESSES) ADDRESSES, 
	SUM(GREENS) GREENS, 
	SUM(SUBSCRIBERS) SUBSCRIBERS, 
	SUM(HSIA) HSIA, 
	SUM(IPTV) IPTV, 
	SUM(VOIP) VOIP, 
	SUM(HSIA_REVENUE) HSIA_REVENUE, 
	SUM(IPTV_REVENUE) IPTV_REVENUE, 
	SUM(VOIP_REVENUE) VOIP_REVENUE, 
	SUM(OTHER_UVERSE_REVENUE) OTHER_UVERSE_REVENUE, 
	SUM(TOTAL_REVENUE) TOTAL_REVENUE,
	100*SUM(GREENS) /((SUM(ADDRESSES) + 0.01)) LIT,
	100*SUM(SUBSCRIBERS)/((SUM(ADDRESSES) + 0.01)) INSERVICE,
    100*(INSERVICE/(LIT+0.01)) SATURATION,    
	100*SUM(IPTV_REVENUE) /(SUM(TOTAL_REVENUE) + 0.01) IPTVPercentage,
	100*SUM(HSIA_REVENUE) /((SUM(TOTAL_REVENUE) + 0.01)) HSIAPercentage,
	100*SUM(VOIP_REVENUE) /((SUM(TOTAL_REVENUE) + 0.01))  VOIPPercentage,
	100*SUM(OTHER_UVERSE_REVENUE) /((SUM(TOTAL_REVENUE) + 0.01))  OtherPercentage,
	SUM(TOTAL_REVENUE) /(SUM(SUBSCRIBERS) + 0.01)  TotalPerSubscriber,
	SUM(IPTV_REVENUE) /(SUM(IPTV) + 0.01) IPTVPerSubscriber,
	SUM(HSIA_REVENUE) /(SUM(HSIA) + 0.01) HSIAPerSubscriber,
	SUM(VOIP_REVENUE) /(SUM(VOIP) + 0.01)  VOIPPerSubscriber,
	SUM(OTHER_UVERSE_REVENUE) /(SUM(SUBSCRIBERS) + 0.01)  OtherPerSubscriber
FROM ({0}) AS DETAIL_ROWS
GROUP	BY ACCT_MGR, ROLLUP_TYPE
ORDER BY ACCT_MGR
;
--5
SELECT 
	PORTFOLIO_NM,
	'PORTFOLIO_NM' ROLLUP_TYPE,
	MAX(REGION)  REGION,
	MAX(MARKET) MARKET,	
	MAX(STATE_CD) STATE_CD, 
	MAX(ACCT_MGR) ACCT_MGR, 
	MAX(AREA_MGR) AREA_MGR,	
	PORTFOLIO_NM PROPERTY_NM, 
	COUNT(TRAC_ID) TRAC_ID, 
	SUM(ADDRESSES) ADDRESSES, 
	SUM(GREENS) GREENS, 
	SUM(SUBSCRIBERS) SUBSCRIBERS, 
	SUM(HSIA) HSIA, 
	SUM(IPTV) IPTV, 
	SUM(VOIP) VOIP, 
	SUM(HSIA_REVENUE) HSIA_REVENUE, 
	SUM(IPTV_REVENUE) IPTV_REVENUE, 
	SUM(VOIP_REVENUE) VOIP_REVENUE, 
	SUM(OTHER_UVERSE_REVENUE) OTHER_UVERSE_REVENUE, 
	SUM(TOTAL_REVENUE) TOTAL_REVENUE,
	100*SUM(GREENS) /((SUM(ADDRESSES) + 0.01)) LIT,
	100*SUM(SUBSCRIBERS)/((SUM(ADDRESSES) + 0.01)) INSERVICE,
    100*(INSERVICE/(LIT+0.01)) SATURATION,    
	100*SUM(IPTV_REVENUE) /(SUM(TOTAL_REVENUE) + 0.01) IPTVPercentage,
	100*SUM(HSIA_REVENUE) /((SUM(TOTAL_REVENUE) + 0.01)) HSIAPercentage,
	100*SUM(VOIP_REVENUE) /((SUM(TOTAL_REVENUE) + 0.01))  VOIPPercentage,
	100*SUM(OTHER_UVERSE_REVENUE) /((SUM(TOTAL_REVENUE) + 0.01))  OtherPercentage,
	SUM(TOTAL_REVENUE) /(SUM(SUBSCRIBERS) + 0.01)  TotalPerSubscriber,
	SUM(IPTV_REVENUE) /(SUM(IPTV) + 0.01) IPTVPerSubscriber,
	SUM(HSIA_REVENUE) /(SUM(HSIA) + 0.01) HSIAPerSubscriber,
	SUM(VOIP_REVENUE) /(SUM(VOIP) + 0.01)  VOIPPerSubscriber,
	SUM(OTHER_UVERSE_REVENUE) /(SUM(SUBSCRIBERS) + 0.01)  OtherPerSubscriber
FROM ({0}) AS DETAIL_ROWS
GROUP	BY PORTFOLIO_NM, ROLLUP_TYPE
ORDER BY PORTFOLIO_NM
;
